<?php

// Version 1.0.8
// Date 2013-03-04

class db_mysqli {

	private $mysqli = null;
	private $booleans = array('verified', 'paid', 'completed', 'linked', 'priceperperson', 'iscompany');

	public function  __construct() {
		include_once 'config.php';
		global $mysqli;
		$this->mysqli = new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
		if (mysqli_connect_errno()) {
			printf("Connect failed: %s\n", mysqli_connect_error());
			exit();
		}
	}

	public function Close() {
		$this->mysqli->close();
	}

	public function Query($query, $close = true) {
		$result = $this->mysqli->query($query);
		if ($close) $this->mysqli->close();
		return $result;
	}

	public function LastID() {
		return $this->mysqli->insert_id;
	}

	/*
		usage:
			$cn = new db_mysql();
			$array = $cn->Data('news');
			$array = $cn->Data('select headline from news order by date desc');
	*/

	public function Data($query, $close = true) {
		if (strpos($query, ' ') === false) $query = "select * from $query order by id desc";
		//$this->mysqli->real_escape_string($query);
		$result = $this->mysqli->query($query);
		$arr = array();
		if ($result == true) while ($row = $result->fetch_assoc()) $arr[] = $row;
		if (is_object($result)) $result->close();
		if ($close) $this->mysqli->close();
		return $arr;
	}

	/*

	*/

	public function Types($query, $close = false) {
		if (strpos($query, ' ') === false) $query = "select * from $query order by id desc";
		$arr = array();
		if ($result = $this->mysqli->query($query)) {
			$finfo = $result->fetch_fields();
			foreach ($finfo as $val) {
				$arr[] = array('type'=>$val->type, 'label'=>$val->name);
			}
		}
		if (is_object($result)) $result->close();
		if ($close) $this->mysqli->close();
		return $arr;
	}

	/*

	*/

	public function Count($query, $close = false) {
		$this->mysqli->real_escape_string($query);
		$result = $this->mysqli->query($query);
		$num = $result->num_rows;
		$result->close();
		if ($close) $this->mysqli->close();
		return $num;
	}

	/*
		usage: $result = $cn->Add('news', array('date'=>'2013-01-01', 'headline'=>'Hello'));
	*/

	public function Add($table, $data, $close = true, $exclude = array()) {
		$table = $this->mysqli->real_escape_string($table);
		$keys = array();
		$vals = array();
		while (list($key, $value) = each($data)) {
			if (!in_array($key, $exclude)) {
				$key = $this->mysqli->real_escape_string($key);
				$value = $this->mysqli->real_escape_string($value);
				$keys[] = $key;
				$vals[] = $value;
			}
		}
		$str_keys = implode($keys, ', ');
		$str_vals = implode($vals, "', '");
		$query = "insert into $table ($str_keys) values ('$str_vals')";
		$result = $this->mysqli->query($query);
		if ($close) $this->mysqli->close();
		return($result == true);
	}

	/*
		usage: $result = $connection->Update('news', 1, array('headline'=>'Goodbye', 'date'=>'2013-01-02'));
	*/

	public function Update($table, $id, $data, $close = true, $exclude = array()) {
		$table = $this->mysqli->real_escape_string($table);
		$id = $this->mysqli->real_escape_string($id);
		$query = "update $table set ";	
		while (list($key,$value) = each($data)) {
			if (!in_array($key, $exclude)) {
				$key = $this->mysqli->real_escape_string($key);
				$value = $this->mysqli->real_escape_string($value);
				$query .= "$key='$value',";
			}
		}
		$query = substr($query, 0, -1);
		$query .= " where id=$id";
		$result = $this->mysqli->query($query);
		if ($close) $this->mysqli->close();
		return($result == true);
	}

	/*
		usage: $result = $cn->Delete('news', 1);
	*/
	
	public function Delete($table, $id, $close = true) {
		$table = $this->mysqli->real_escape_string($table);
		$id = $this->mysqli->real_escape_string($id);
		$result = $this->mysqli->query("delete from $table where id=$id");
		if ($close) $this->mysqli->close();
		return($result == true);
	}

	/*
	*/

	public function OutputJSON($query, $close = true) {
		$arr = $this->Data($query, $close);
		return json_encode($arr);
	}

	/*
	*/

	public function OutputXML($query, $nodes = array(), $cdata = true, $special = true, $close = true) {
		$arr = $this->Data($query, $close);
		$mnode = sizeof($nodes) > 0 ? $nodes[0] : 'xml';
		$str = "<?xml version=\"1.0\"?>\n<$mnode>\n";
		foreach($arr as $row) {
			$rnode = sizeof($nodes) > 1 ? $nodes[1] : 'row';
			$str .= "\t<$rnode>\n";
			foreach($row as $key => $value) {
				$value = $special ? htmlentities($value) : $value;
				if ($cdata) $value = "<![CDATA[$value]]>";
				$str .= "\t\t<$key>$value</$key>\n";
			}
			$str .= "\t</$rnode>\n";
		}
		$str .= "</$mnode>";
		return($str);
	}

	/*
	*/

	public function OutputTable($query, $labels = array(), $funct = array(), $class = '', $close = true) {
		$arr = $this->Data($query, $close);
		$types = $this->Types($query, $close);
		$str = '';
		if (sizeof($arr) > 0) {
			$str = "\n<table class=\"$class\">\n<thead>\n<tr>\n";
			if (sizeof($labels) == 0) {
				foreach($arr[0] as $key => $value) $str .= "<th>$key</th>\n";
			} else {
				foreach($labels as $label) $str .= "<th>$label</th>\n";
			}
			if (sizeof($funct) > 0) $str .= "<th class=\"functions\"></th>\n";
			$str .= "</tr>\n</thead>\n<tbody>\n";
			$i = 0;
			foreach($arr as $row) {
				$i ++;
				$class = $i%2 == 0 ? ' class="alt"' : '';
				$rowid = isset($row['id']) ? $row['id'] : $i;
				$str .= "<tr$class data-id=\"$rowid\">\n";
				$id = 0;
				$k = 0;
				foreach($row as $key => $value) {
					$value = str_replace('src="', 'width="300" src="'.DOC_ROOT, $value);
					if (in_array($types[$k]['label'], $this->booleans) && $types[$k]['type'] == 3) {
						$value = $value == 0 ? 'no' : 'yes';
						$value = "<span class=\"tag $value\">".strtoupper($value)."</span>";
					}
					if ($types[$k]['type'] == 3 && $types[$k]['label'] == 'userid') {
						if ($value == 0) $value = 'Administrator';
						else {
							$ud = $this->Data('select iscompany, name, company from users where id = '.$value, false);
							$value = "[$value] ";
							if ($ud[0]['iscompany'] == 1) $value .= $ud[0]['company'];
							else $value .= $ud[0]['name'];
							$ud = null;
						}
					}
					$str .= "<td class=\"$key\" data-type=\"".$types[$k]."\">$value</td>\n";
					if ($key == 'id') $id = $value;
					$k ++;
				}
				if (sizeof($funct) > 0) {
					$str .= "<td class=\"functions\">\n";
					foreach($funct as $fn) {
						if ($fn == 'subscribe')
							$str .= "<a class=\"$fn\" href=\"".DOC_ROOT.$_SESSION['table']."/$fn/$id\">Subscribe</a>";
						else
							$str .= "<a class=\"$fn\" href=\"".DOC_ROOT.$_SESSION['table']."/$fn/$id\"><img src=\"".DOC_ROOT."img/blank.gif\" alt=\"\" /></a>\n";
					}
					$str .= "</td>\n";
				}
				$str .= "</tr>\n";
			}
			$str .= "</tbody>\n</table>\n";
		}
		//$str = print_r($types);
		return($str);
	}

	/*
	*/

	public function OutputTableItem($query, $labels = array(), $cols = 2, $class = '', $close = true) {
		$arr = $this->Data($query, $close);
		$types = $this->Types($query, $close);
		$str = '';
		if (sizeof($arr) > 0) {
			$str = "\n<table class=\"$class\">\n<tbody>\n";
			$values = array();
			if (sizeof($labels) == 0) {
				foreach($arr[0] as $key => $value) array_push($labels, $key);
			}
			$k = 0;
			foreach($arr[0] as $key => $value) {
				if (in_array($types[$k]['label'], $this->booleans) && $types[$k]['type'] == 3) {
					$value = $value == 0 ? 'no' : 'yes';
					$value = "<span class=\"tag $value\">".strtoupper($value)."</span>";
				}
				if ($types[$k]['type'] == 3 && $types[$k]['label'] == 'userid') {
					if ($value == 0) $value = 'Administrator';
					else {
						$ud = $this->Data('select iscompany, name, company from users where id = '.$value, false);
						$value = "[$value] ";
						if ($ud[0]['iscompany'] == 1) $value .= $ud[0]['company'];
						else $value .= $ud[0]['name'];
						$ud = null;
					}
				}
				array_push($values, $value);
				$k++;
			}
			$k = 0;
			$rows = ceil(sizeof($values) / ($cols / 2));
			for ($i = 0; $i < $rows; $i++) {
				$str .=  "<tr>\n";
				for ($j = 0; $j < $cols / 2; $j ++) {
					$value = '';
					$label = '';
					if ($k < sizeof($values)) {
						$label = $labels[$k];
						$value = str_replace('src="', 'width="300" src="'.DOC_ROOT, $values[$k]);
					}
					$str .= "<td class=\"label\">$label</td>\n";
					$str .= "<td>$value</td>\n";
					$k ++;
				}
				$str .=  "</tr>\n";
			}
			$str .= "</tbody>\n</table>\n";
		}
		return($str);
	}
	
	/*
	*/

	public function AddFormTable($table, $exclude = array(), $labels = array(), $types = array(), $close = true) {
		$excl = implode("','", $exclude);
		$query = "show fields from $table where field not in ('$excl')";
		$arr = $this->Data($query, $close);
		$str = "<table>\n";
		$i = 0;
		foreach($arr as $item) {
			$key = $item['Field'];
			$ret = $this->GetLabelInput($labels, $types, $i, $key);
			$label = $ret[0];
			$input = $ret[1];
			$str .= "<tr>\n<td>$label</td>\n<td>$input</td>\n</tr>\n";
			$i++;
		}
		$str .= "</table>\n";
		return($str);
	}
	
	/*
	*/

	public function AddFormUL($table, $exclude = array(), $labels = array(), $types = array(), $close = true) {
		$excl = implode("','", $exclude);
		$query = "show fields from $table where field not in ('$excl')";
		$arr = $this->Data($query, $close);
		$str = '<ul>';
		$i = 0;
		foreach($arr as $item) {
			$key = $item['Field'];
			$ret = $this->GetLabelInput($labels, $types, $i, $key);
			$label = $ret[0];
			$input = $ret[1];
			$str .= "<li class=\"label\">$label</li><li class=\"value\">$input</li>";
			$i++;
		}
		$str .= '</ul>';
		return($str);
	}

	/*
	*/

	public function AddForm($table, $method = 'post', $action = '', $exclude = array(), $labels = array(), $types = array(), $close = true) {
		$excl = implode("','", $exclude);
		$query = "show fields from $table where field not in ('$excl')";
		$arr = $this->Data($query, $close);
		$str = "<form method=\"$method\" action=\"$action\">\n";
		$i = 0;
		foreach($arr as $item) {
			$key = $item['Field'];
			$ret = $this->GetLabelInput($labels, $types, $i, $key);
			$label = $ret[0];
			$input = $ret[1];
			if ($label != '') $str .= "<label for=\"$key\">$label</label>\n";
			$str.= "$input\n";
			$i++;
		}
		$str .= "<button type=\"submit\" name=\"add-submit\">Add</button>\n";
		$str .= "</form>\n";
		return($str);
	}
	
	/*
		usage: $result = $connection->EditFormTable('select * from stores where id=1');
	*/

	public function EditFormTable($query, $labels = array(), $types = array(), $close = true) {
		$arr = $this->Data($query, $close);
		$str = '<table>';
		$i = 0;
		foreach($arr[0] as $key => $value) {
			$ret = $this->GetLabelInput($labels, $types, $i, $key, $value);
			$label = $ret[0];
			$input = $ret[1];
			$str .= "<tr><td>$label</td><td>$input</td></tr>";
			$i++;
		}
		$str .= '</table>';
		return($str);
	}
	
	/*
		Usage: $result = $connection->EditFormUL('select * from stores where id=1');
	*/

	public function EditFormUL($query, $labels = array(), $types = array(), $close = true) {
		$arr = $this->Data($query, $close);
		$str = '<ul>';
		$i = 0;
		foreach($arr[0] as $key => $value) {
			$ret = $this->GetLabelInput($labels, $types, $i, $key, $value);
			$label = $ret[0];
			$input = $ret[1];
			$str .= "<li class=\"label\">$label</li><li class=\"value\">$input</li>";
			$i++;
		}
		$str .= '</ul>';
		return($str);
	}

	/*
		Usage: $result = $connection->EditForm('select * from stores where id=1');
	*/

	public function EditForm($query, $method = 'post', $action = '', $labels = array(), $types = array(), $close = true) {
		$arr = $this->Data($query, $close);
		$str = "<form method=\"$method\" action=\"$action\">\n";
		$i = 0;
		foreach($arr[0] as $key => $value) {
			$ret = $this->GetLabelInput($labels, $types, $i, $key, $value);
			$label = $ret[0];
			$input = $ret[1];
			if ($label != '') $str .= "<label for=\"$key\">$label</label>\n";
			$str.= "$input\n";
			$i++;
		}
		$str .= "<button type=\"submit\" name=\"edit-submit\">Edit</button>\n";
		$str .= "</form>\n";
		return($str);
	}
	
	//
	private function GetLabelInput($labels, $types, $i, $key, $value = '') {
		$ret = array();
		$label = ($i >= sizeof($labels)) ? $key : $labels[$i];
		if ($i >= sizeof($types)) $input = "<input id=\"$key\" name=\"$key\" type=\"text\" value=\"$value\" />";
		else {
			$type = explode('|', $types[$i]);
			switch($type[0]) {
			
				case 'textarea':
					$input =  "<textarea name=\"$key\">$value</textarea>";
					break;
				
				case 'readonly':
					if (sizeof($type) > 1) $value = $type[1];
					$input =  "<input id=\"$key\" name=\"$key\" type=\"text\" value=\"$value\" readonly />";
					break;
				
				case 'integer':
					if ($value == '') $value = '0';
					$input = "<input id=\"$key\" name=\"$key\" type=\"text\" class=\"input-integer\" value=\"$value\" />";
					break;

				case 'telephone':
					if ($value == '') $value = '0';
					$input = "<input id=\"$key\" name=\"$key\" type=\"text\" class=\"input-tel\" value=\"$value\" />";
					break;

				case 'decimal':
					if ($value == '') $value = '0.00';
					$input = "<input id=\"$key\" name=\"$key\" type=\"text\" class=\"input-decimal\" value=\"$value\" />";
					break;

				case 'date':
					$value = date('Y-m-d');
					$input = "<input id=\"$key\" name=\"$key\" type=\"text\" class=\"input-date\" value=\"$value\" readonly />\n";
					break;

				case 'datedisabled':
					$value = date('Y-m-d');
					$input = "<input id=\"$key\" name=\"$key\" type=\"text\" class=\"input-date-disabled\" value=\"$value\" readonly />\n";
					break;

				case 'time':
					if ($value == '') $value = '00:00';
					$input = "<input id=\"$key\" name=\"$key\" type=\"text\" class=\"input-time\" value=\"$value\" />";
					break;

				case 'date-time':
					$value = date('Y-m-d');
					$input = "<input type=\"text\" class=\"input-date\" value=\"$value\" readonly />\n";
					$value = date('H');
					$input .= "<p class='sep small'> Time</p><input type=\"text\" class=\"input-hours\" value=\"$value\" />";
					$value = date('i');
					$input .= "<p class='sep'>:</p><input type=\"text\" class=\"input-mins\" value=\"$value\" />";
					$value = date('Y-m-d H:i:00');
					$input .= "<input id=\"$key\" name=\"$key\" type=\"hidden\" value=\"$value\" />\n";
					break;

				case 'checkbox':
					$checked = $value == 1 ? ' check' : '';
					$input = "<span class=\"checkbox$checked\"><input id=\"$key\" type=\"hidden\" name=\"$key\" value=\"$value\" /></span>\n";
					break;
				
				case 'dropdown':
					$options = explode(',', $type[1]);
					$selprov = $value == '' ? $options[0] : $value;
					$input = "<span class=\"select\">\n";
					$input .= "<input id=\"$key\" type=\"hidden\" name=\"$key\" value=\"$selprov\" />\n";
					$input .= "<span>$selprov</span>\n<ul>\n";
					foreach ($options as $option) {
						$input .= "<li data-value=\"$option\">$option</li>\n";
					}
					$input .= "</ul>\n</span>";
					break;

				case 'tablelinkdropdown':
					$link_tbl = $type[1];
					$link_val = $type[2];
					$link_lbl = $type[3];
					$blank = sizeof($type) == 5;
					$data = $this->Data('select * from '.$link_tbl.' order by id desc', false);
					$value = !$blank ? $data[0][$link_val] : '';
					$lbl = !$blank ? $data[0][$link_lbl] : 'none';
					$input = "<span class=\"select\">\n";
					$input .= "<input id=\"$key\" type=\"hidden\" name=\"$key\" value=\"$value\" />\n";
					$input .= "<span>$lbl</span>\n<ul>\n";
					if ($blank) $input .= "<li data-value=\"0\">none</li>\n";
					foreach ($data as $row) {
						$input .= "<li data-value=\"".$row[$link_val]."\">".$row[$link_lbl]."</li>\n";
					}
					$input .= "</ul>\n</span>";
					break;

				case 'tablelinkmulti':
					$link_tbl = $type[1];
					$link_val = $type[2];
					$link_lbl = $type[3];
					$data = $this->Data('select * from '.$link_tbl.' order by id desc', false);
					
					$input = "<select id=\"$key\" name=\"".$key."[]\" multiple>\n";
					foreach ($data as $row) {
						$input .= "<option value=\"".$row[$link_val]."\">".$row[$link_lbl]."</option>\n";
					}
					$input .= "</select>\n";
					/*$input = "<span class=\"select\">\n";
					$input .= "<input id=\"$key\" type=\"hidden\" name=\"$key\" value=\"$value\" />\n";
					$input .= "<span>$lbl</span>\n<ul>\n";
					if ($blank) $input .= "<li data-value=\"0\">none</li>\n";
					foreach ($data as $row) {
						$input .= "<li data-value=\"".$row[$link_val]."\">".$row[$link_lbl]."</li>\n";
					}
					$input .= "</ul>\n</span>";*/
					break;

				case 'hidden':
					if ($value == '') $value = $type[1];
					$input = "<input id=\"$key\" name=\"$key\" type=\"hidden\" value=\"$value\" />";
					break;

				default: $input =  "<input id=\"$key\" name=\"$key\" type=\"".$types[$i]."\" value=\"$value\" />"; break;
			}
		}
		$ret[0] = $label;
		$ret[1] = $input;
		return($ret);
	}
}

?>